﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace MySqlCode.Handler
{
    public class MySQLHelper
    {
        public string Connection_String { get; set; }

        private MySqlConnection _SqlConnection;
        private MySqlConnection SqlConnection
        {
            get
            {
                if (_SqlConnection == null)
                {
                    _SqlConnection = new MySqlConnection(Connection_String);
                }
                if (_SqlConnection.State == ConnectionState.Closed)
                {
                    _SqlConnection.Open();
                }
                return _SqlConnection;
            }
            set { _SqlConnection = value; }
        }

        public MySQLHelper(Model.DataInfo model)
        {
            string connStr = $"server={model.Host};port={model.Port};user id={model.User};password={model.Pwd};";
            if (!string.IsNullOrWhiteSpace(connStr))
            {
                Connection_String = connStr;
            }
        }

        public bool TestOpen()
        {
            try
            {
                MySqlConnection conn = new MySqlConnection(Connection_String);
                conn.Open();
                conn.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public List<string> ShowDatabase()
        {
            string sql = "show databases;";
            DataTable table = Query(sql);
            List<string> dbs = new List<string>();
            foreach (DataRow dr in table.Rows)
            {
                dbs.Add(dr[0].ToString());
            }
            return dbs;
        }

        public List<string> ShowTables(string name)
        {
            string sql = $"use {name}; show tables;";
            DataTable table = Query(sql);
            List<string> dbs = new List<string>();
            foreach (DataRow dr in table.Rows)
            {
                dbs.Add(dr[0].ToString());
            }
            return dbs;
        }

        public List<Model.Column> AllFieldInfo(string name)
        {
            string sql = $"show full columns from {name};";
            DataTable table = Query(sql);
            List<Model.Column> list = new List<Model.Column>();
            foreach (DataRow dr in table.Rows)
            {
                var col = new Model.Column()
                {
                    Comment = string.IsNullOrWhiteSpace(dr["Comment"].ToString())
                    ? dr["Field"].ToString().HumpNaming() : dr["Comment"].ToString(),
                    Field = dr["Field"].ToString().HumpNaming(),
                    IsNull = dr["Null"].ToString() == "YES" ? true : false,
                    Type = dr["Type"].ToString().ExchangeCsharpType()
                };
                list.Add(col);
            }
            return list;
        }

        public DataTable Query(string sql, MySqlParameter[] parameters = null)
        {
            using (MySqlCommand comm = new MySqlCommand(sql, SqlConnection))
            {
                comm.Connection = SqlConnection;
                using (MySqlDataAdapter da = new MySqlDataAdapter(comm))
                {
                    if (parameters != null)
                    {
                        foreach (IDataParameter parameter in parameters)
                        {
                            comm.Parameters.Add(parameter);
                        }
                    }
                    using (DataSet ds = new DataSet())
                    {
                        da.Fill(ds);
                        using (DataTable table = ds.Tables[0])
                        {
                            SqlConnection.Close();
                            return table;
                        }
                    }
                }
            }
        }

        public int ExecuteNonQuery(string sql, MySqlParameter[] parameters = null)
        {
            using (MySqlCommand comm = new MySqlCommand(sql, SqlConnection))
            {
                comm.Connection = SqlConnection;
                if (parameters != null)
                {
                    foreach (IDataParameter parameter in parameters)
                    {
                        comm.Parameters.Add(parameter);
                    }
                }
                int number = comm.ExecuteNonQuery();
                SqlConnection.Close();
                return number;
            }
        }

        public object ExecuteScalar(string sql, MySqlParameter[] parameters = null)
        {
            using (MySqlCommand comm = new MySqlCommand(sql, SqlConnection))
            {
                comm.Connection = SqlConnection;
                if (parameters != null)
                {
                    foreach (IDataParameter parameter in parameters)
                    {
                        comm.Parameters.Add(parameter);
                    }
                }
                object obj = comm.ExecuteScalar();
                SqlConnection.Close();
                return obj;
            }
        }
    }
}